package com.entity.excel;

import com.entity.ShoufeixinxiEntity;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

/**
 * 批量添加100万数据
 */
public class DataDemo {
    public static void main(String[] args) {
        List<ShoufeixinxiEntity> list = new ArrayList<>();
        long start = System.currentTimeMillis();
        System.out.println("开始生成一百万数据");
        //创建JDBC连接驱动
        for (int i = 0; i < 1000000; i++) {
            ShoufeixinxiEntity shoufeixinxiEntity = new ShoufeixinxiEntity();
            shoufeixinxiEntity.setShoufeixinxiTypes(4);
            shoufeixinxiEntity.setMingmuName("测试"+i);
            shoufeixinxiEntity.setShoufeixinxiShishouMoney(100d);
            shoufeixinxiEntity.setShoufeixinxiYushouMoney(100d);
            shoufeixinxiEntity.setInsertTime(new Date());
            shoufeixinxiEntity.setCreateTime(new Date());
            list.add(shoufeixinxiEntity);
        }
        System.out.println("生成一百万数据 耗时："+(System.currentTimeMillis()-start)+"ms");
        try {
            testForRemote(list);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static void testForRemote(List<ShoufeixinxiEntity> list2) throws Exception {
        try {
            //准白sql语句
            String sql = "INSERT INTO `caiwuguanli`.`shoufeixinxi`(`mingmu_name`, `shoufeixinxi_types`, `insert_time`, `shoufeixinxi_yushou_money`, `shoufeixinxi_shishou_money`, `create_time`) VALUES (?,?,?,?,?,?)";
            Connection con = null;
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取到远程服务器的连接
            con = DriverManager.getConnection("jdbc:mysql://192.168.1.10:3310/caiwuguanli?useUnicode=true&"+
                            "characterEncoding=utf-8&useSSL=false&rewriteBatchedStatements=true",
                    "root","Xqp@031719");
            //设置非自动提交事务
            con.setAutoCommit(false);

            PreparedStatement pstat = con.prepareStatement(sql);

            //获得模拟数据
            long start = System.currentTimeMillis();
            for (int i=0; i<list2.size(); i++) {

                ShoufeixinxiEntity shoufeixinxi = list2.get(i);
                pstat.setString(1, shoufeixinxi.getMingmuName());
                pstat.setInt(2, shoufeixinxi.getShoufeixinxiTypes());
                pstat.setDate(3, new java.sql.Date(shoufeixinxi.getInsertTime().getTime()));
                pstat.setDouble(4, shoufeixinxi.getShoufeixinxiYushouMoney());
                pstat.setDouble(5, shoufeixinxi.getShoufeixinxiShishouMoney());
                pstat.setDate(6, new java.sql.Date(shoufeixinxi.getInsertTime().getTime()));

                //10w提交一次
                pstat.addBatch();
                if(i % 100000 == 0){
                    pstat.executeBatch();
                    pstat.clearBatch();
                }
            }
            pstat.executeBatch(); //执行批处理
            pstat.clearBatch();  //清空批处理
            con.commit();
            long end = System.currentTimeMillis();
            pstat.close();
            con.close();
            System.out.print((end-start)/1000+"秒。");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}
